<?php

ini_set('error_reporting', E_ERROR);
ini_set('display_errors', 'On');


$query = <<<QUERY

SELECT
        t.locname as name,
        t.locstr as address,
        t.loccity,
        t.zip,
        t.facilityde as description,
	c.subrelease as material,
        t.notificati as notification,
        t.dateclose as closed,
        t.depthgw,
        AsKML(t.the_geom) as kml
FROM
        tanks as t,
	tanks_cause_release as c
WHERE 
        t.lustkey = c.lustkey
ORDER BY
        t.locname,
	t.notificati
QUERY;


header('Content-type: application/vnd.google-earth.kml+xml');

$conn = pg_connect("dbname=rvha user=pgadmin password=pgadmin");
	if (!$conn)
	{
       	 	print "Unable to Connect to DB";
	        exit;
	}


$result = pg_query($query);

class Incident {
	var $material;
	var $notification;
	var $closed;
	var $depthgw;
}

class Tank {
	var $name;
	var $address;
	var $city;
	var $zip;
	var $description;
	var $kml;
	var $incidents = array(); // an array of Incident(s)
}

$tanks = array();

while($arr = pg_fetch_array($result))
{
	$tank = new Tank();

	$tank->kml = $arr["kml"];
	$tank->name = $arr["name"];
	$tank->address = $arr["address"];
	$tank->city = $arr["loccity"];
	$tank->zip = $arr["zip"];
	$tank->description = $arr["description"];

	/* put meterial, notif, closed, depthgh into incident */
	$incident = new Incident();

	$material = $arr["material"];
	if( $material == "" )
	{
		$material = "Unknown";
	}
	
	$notification = $arr["notification"];

	$closed = $arr["closed"];
	if( $closed == "" )
	{
		$closed = "Unknown";
	}

	$depthgw = $arr["depthgw"];
	if( $depthgw == "" )
	{
		$depthgw = "Unknown";
	}

	$incident->material = $material;
	$incident->notification = $notification;
	$incident->closed = $closed;
	$incident->depthgw = $depthgw;

	// handle duplicates, usually based on material and date
	if( $tanks[$tank->name] != "" )
	{
		/*
		* There may be mulitple lines with different materials or notification/close dates
		* so just gang them all together as multiple incidents
		*
		*/	
		$oldTank = $tanks[$tank->name];
		array_push($oldTank->incidents, $incident);
		$tanks[$oldTank->name] = $oldTank;
	} 
	else
	{
		array_push($tank->incidents, $incident);
		$tanks[$tank->name] = $tank;
	}
	
}

print <<<KML_TOP
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.2">
<Document>
	<name>Salt Lake County - Open Leaking Underground Storage Tanks</name>
	<description>This file is a composite data file based on information from the Utah Automated Geographic Reference Center (AGRC). Information regarding the 
	open tanks can be found at ftp://ftp.agrc.state.ut.us/SGID_Vector/MetadataHTML/SGID_U100_LUST_OpenTanks.html.
	Information regarding the cause of leaking can be found at ftp://ftp.agrc.state.ut.us/SGID_Vector/MetadataHTML/SGID_U100_LUST_CauseOfRelease.html</description>
	<Style id="trb818" x="12" y="12" xunits="pixels" yunits="pixels">
		<IconStyle>
			<Icon>
				<href>lst.png</href>
			</Icon>
			<hotSpot x="0" y="0" xunits="pixels" yunits="pixels"/>
		</IconStyle>
	</Style>
KML_TOP;

/* loop over tanks */
$counter = 0;
foreach( $tanks as $tank )
{

	$name = $tank->name;
	$kml = $tank->kml;
	$description = $tank->description;
	$address = $tank->address;
	$zip = $tank->zip;
	
	$incidents = $tank->incidents;

	$cdata = "\n<![CDATA[";
	$cdata .= "\n $address $zip<br/>";
	$cdata .= "\n Facility description: $description<br/>";
	$cdata .= "\n Release(s) at this site:<br/>";
	
	foreach( $incidents as $incident)
	{
		$cdata .= "\n<blockquote>";
		$cdata .= "\n Material released: " . $incident->material . "<br/>";
		$cdata .= "\n Notified on: " . $incident->notification. "<br/>";
		$cdata .= "\n Closed on: " . $incident->closed. "<br/>";
		$cdata .= "\n Depth to ground water: " . $incident->depthgw. "<br/>";
		$cdata .= "\n</blockquote>";
	}	

	$cdata .= "\n]]>";

	print "\n<Placemark id=\"$counter\">";
	print "\n<name>".htmlentities($name)."</name>";
	print "\n<description>$cdata</description>";
	print "<styleUrl>#trb818</styleUrl>";
	print "$kml";
	print "</Placemark>";
	$counter++;
}

print <<<KML_BOTTOM
</Document>
</kml>

KML_BOTTOM;

?>

